package com.ksd.common.excel;

import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import java.io.File;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class XmlToSongExcel {
    public static void main(String[] args) {
        try {
            // 指定包含XML文件的文件夹路径
            String folderPath = "E:\\政企后台电影\\硬盘数据资源\\usbData\\歌曲资源";

            // 解析文件夹中所有的XML文件，获取所有歌曲列表
            List<Song> allSongs = parseAllXmlFiles(folderPath);

            // 将所有歌曲列表写入Excel文件
            writeToExcel(allSongs, "E:\\政企后台电影\\硬盘数据资源\\usbData\\歌曲资源\\歌曲汇总.xlsx");

            System.out.println("所有数据已成功写入Excel文件！共处理 " + allSongs.size() + " 首歌曲");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    // 遍历文件夹中的所有XML文件并解析
    private static List<Song> parseAllXmlFiles(String folderPath) throws Exception {
        List<Song> allSongs = new ArrayList<>();

        File folder = new File(folderPath);
        File[] files = folder.listFiles();

        if (files != null) {
            for (File file : files) {
                // 只处理XML文件
                if (file.isFile() && file.getName().toLowerCase().endsWith(".xml.done")) {
                    System.out.println("正在解析文件: " + file.getAbsolutePath());
                    List<Song> songsFromFile = parseSingleXmlFile(file);
                    allSongs.addAll(songsFromFile);
                } else if (file.isDirectory()) {
                    // 如果需要递归处理子文件夹，取消下面这行的注释
                     allSongs.addAll(parseAllXmlFiles(file.getAbsolutePath()));
                }
            }
        } else {
            System.out.println("指定的文件夹路径不存在或无法访问: " + folderPath);
        }

        return allSongs;
    }

    // 解析单个XML文件，返回歌曲列表
    private static List<Song> parseSingleXmlFile(File xmlFile) throws Exception {
        List<Song> songs = new ArrayList<>();

        DocumentBuilderFactory dbFactory = DocumentBuilderFactory.newInstance();
        DocumentBuilder dBuilder = dbFactory.newDocumentBuilder();
        Document doc = dBuilder.parse(xmlFile);
        doc.getDocumentElement().normalize();

        // 获取Content元素
        Element contentElement = (Element) doc.getElementsByTagName("Content").item(0);
        if (contentElement == null) {
            System.out.println("警告: 文件 " + xmlFile.getName() + " 中未找到Content元素，已跳过");
            return songs;
        }

        // 获取主分类、子分类、专辑标题、专辑封面地址
        String category = getElementText(contentElement, "Category");
        String subcategory = getElementText(contentElement, "Subcategory");
        String albumTitle = getElementText(contentElement, "Title");
        String albumCoverUrl = getElementText(contentElement, "ImageFile");

        // 获取所有Episode元素
        NodeList episodeList = contentElement.getElementsByTagName("Episode");
        //处理资源路径/wenhua/vod/cyyx
        String start = "/wenhua/vod/cyyx";
        albumCoverUrl = "/歌曲资源" + albumCoverUrl.substring(start.length());
        // 遍历每个Episode元素，提取歌曲信息
        for (int i = 0; i < episodeList.getLength(); i++) {
            Node episodeNode = episodeList.item(i);

            if (episodeNode.getNodeType() == Node.ELEMENT_NODE) {
                Element episodeElement = (Element) episodeNode;

                String title = getElementText(episodeElement, "Title");
                String singer = getElementText(episodeElement, "Singer");
                String songUrl = getElementText(episodeElement, "VideoFile");
                String lyricUrl = getElementText(episodeElement, "LyricFile");



                songUrl = "/歌曲资源" + songUrl.substring(start.length());
                lyricUrl = "/歌曲资源" + lyricUrl.substring(start.length());
                // 处理子分类
                if (subcategory != null && subcategory.contains("/")) {
                    String[] split = subcategory.split("/");
                    subcategory = split[0];
                }

                // 创建Song对象并添加到列表
                Song song = new Song(title, singer, subcategory,
                        albumTitle, albumCoverUrl, songUrl, lyricUrl);
                songs.add(song);
            }
        }

        return songs;
    }

    // 安全获取元素文本内容的工具方法
    private static String getElementText(Element parent, String tagName) {
        NodeList nodeList = parent.getElementsByTagName(tagName);
        if (nodeList != null && nodeList.getLength() > 0) {
            return nodeList.item(0).getTextContent();
        }
        return ""; // 或者返回null，根据需要处理
    }

    // 将歌曲列表写入Excel文件
    private static void writeToExcel(List<Song> songs, String excelFilePath) throws Exception {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("歌曲信息");

        // 创建标题行
        Row headerRow = sheet.createRow(0);
        String[] headers = {"歌曲名称", "歌手", "音乐类型", "专辑标题", "专辑封面地址", "歌曲地址", "歌词地址"};
        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
            // 设置标题单元格样式
            CellStyle headerStyle = workbook.createCellStyle();
            Font font = workbook.createFont();
            font.setBold(true);
            headerStyle.setFont(font);
            cell.setCellStyle(headerStyle);
        }

        // 写入歌曲数据
        for (int i = 0; i < songs.size(); i++) {
            Song song = songs.get(i);
            Row row = sheet.createRow(i + 1);

            row.createCell(0).setCellValue(song.getTitle());
            row.createCell(1).setCellValue(song.getSinger());
//            row.createCell(2).setCellValue(song.getCategory());
            row.createCell(2).setCellValue(song.getSubcategory());
            row.createCell(3).setCellValue(song.getAlbumTitle());
            row.createCell(4).setCellValue(song.getAlbumCoverUrl());
            row.createCell(5).setCellValue(song.getSongUrl());
            row.createCell(6).setCellValue(song.getLyricUrl());
        }

        // 自动调整列宽
        for (int i = 0; i < headers.length; i++) {
            sheet.autoSizeColumn(i);
        }

        // 写入文件
        try (java.io.FileOutputStream outputStream = new java.io.FileOutputStream(excelFilePath)) {
            workbook.write(outputStream);
        }
        workbook.close();
    }
}

// 需要配套的Song类
class Song {
    private String title;
    private String singer;
//    private String category;
    private String subcategory;
    private String albumTitle;
    private String albumCoverUrl;
    private String songUrl;
    private String lyricUrl;

    public Song(String title, String singer, String subcategory,
                String albumTitle, String albumCoverUrl, String songUrl, String lyricUrl) {
        this.title = title;
        this.singer = singer;
//        this.category = category;
        this.subcategory = subcategory;
        this.albumTitle = albumTitle;
        this.albumCoverUrl = albumCoverUrl;
        this.songUrl = songUrl;
        this.lyricUrl = lyricUrl;
    }

    // Getter方法
    public String getTitle() { return title; }
    public String getSinger() { return singer; }
//    public String getCategory() { return category; }
    public String getSubcategory() { return subcategory; }
    public String getAlbumTitle() { return albumTitle; }
    public String getAlbumCoverUrl() { return albumCoverUrl; }
    public String getSongUrl() { return songUrl; }
    public String getLyricUrl() { return lyricUrl; }
}
